package com.zfzs.post.modules.post.repository;

import com.zfzs.post.modules.post.domain.TerminalInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author ck
 * @date 2019-09-27
 */
public interface TerminalInfoRepository extends JpaRepository<TerminalInfo, Long>, JpaSpecificationExecutor {
    //根据sn查询
    TerminalInfo findBySn(String sn);

    //根据商户号查询
    TerminalInfo findByNumbers(String numbers);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "update post_terminal_info set agent_id =?1 where sn in ?2")
    void updateTerminalsAgent(Long agentId, String[] sns);

    List<TerminalInfo> findBySnBetweenAndChannelId(String startSn, String endSn, Long channelId);

    @Query(value = "select COUNT(b.is_activation)  from post_terminal_info b where b.agent_id in (?1) and b.activation_date >= ?2 and b.activation_date <= ?3 and b.is_activation = ?4", nativeQuery = true)
    String getActivationCount(List<Long> agnetIds, String startDate, String endDate, Integer isActivation);

    @Query(value = "SELECT\n" +
            "( " +
            "ROUND( " +
            "IFNULL( " +
            "( " +
            "SELECT " +
            "sum(t.amount) " +
            "FROM " +
            "transaction_info t " +
            "WHERE " +
            "t.agent_id IN (?1) " +
            "AND t.channel_id = ?4 " +
            "AND t.transaction_time >= ?2 " +
            "AND t.transaction_time <= ?3 " +
            ") / ( " +
            "SELECT " +
            "count(DISTINCT t.sn) " +
            "FROM " +
            "transaction_info t " +
            "WHERE " +
            "t.agent_id IN (?1) " +
            "AND t.channel_id = ?4 " +
            "AND t.transaction_time >= ?2 " +
            "AND t.transaction_time <= ?3 " +
            ")," +
            "0" +
            ")," +
            "2" +
            ")" +
            ") AS average", nativeQuery = true)
    String getAverageSum(List<Long> ids, String startDate, String endDate, Long channelId);
}